﻿using System;
using System.Linq;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using InterpriseSuiteEcommerceCommon.DTO;
using InterpriseSuiteEcommerceCommon.Extensions;
using System.Text;

namespace InterpriseSuiteEcommerceCommon.DataAccess
{
    public class GiftRegistryDA
    {
        public static IEnumerable<GiftRegistry> GetAllGiftRegistries(Guid? contactGuid, string webSiteCode, CultureInfo cultureInfo)
        {
            var lstGiftRegistry = new List<GiftRegistry>();
            using (var con = new SqlConnection(DB.GetDBConn()))
            {
                con.Open();

                IDataReader reader = null;
                //Per contact
                if (contactGuid.HasValue)
                {
                    reader = DB.GetRSFormat(con, "SELECT gr.*, (cc.ContactFirstName + ' ' + cc.ContactLastName) FullName FROM EcommerceGiftRegistry gr with (NOLOCK) " +
                                                 "INNER JOIN CRmContact cc with (NOLOCK) ON cc.ContactGuid = gr.ContactGuid WHERE gr.WebsiteCode = {0} and gr.contactguid = {1}", DB.SQuote(webSiteCode), DB.SQuote(contactGuid.ToString()));
                }
                else //All
                {
                    reader = DB.GetRSFormat(con, "SELECT gr.*, (cc.ContactFirstName + ' ' + cc.ContactLastName) FullName FROM EcommerceGiftRegistry gr with (NOLOCK) INNER JOIN " +
                                                 "CRmContact ON gr.ContactGuid = cc.ContactGuid WHERE gr.WebsiteCode = {0}", DB.SQuote(webSiteCode));
                }

                while (reader.Read()) { lstGiftRegistry.Add(PopulateGiftRegistry(reader)); }

                reader.Close();
                con.Close();
            }

            return lstGiftRegistry;
        }

        public static void SaveGiftRegistry(GiftRegistry giftRegistry)
        {
            using (var con = new SqlConnection(DB.GetDBConn()))
            {
                con.Open();

                DB.ExecuteSQL(string.Format("INSERT INTO EcommerceGiftRegistry(RegistryID, ContactGUID, StartDate, EndDate, Title, Message, PictureFileName, IsPrivate,GuestPassword, CustomURLPostfix, WebsiteCode, IsActive) values({0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11})",
                    DB.SQuote(giftRegistry.RegistryID.ToString()), DB.SQuote(giftRegistry.ContactGUID.ToString()), DB.SQuote(giftRegistry.StartDate.Value.ToString()),
                    DB.SQuote(giftRegistry.EndDate.Value.ToString()), DB.SQuote(giftRegistry.Title), DB.SQuote(giftRegistry.Message), DB.SQuote(giftRegistry.PictureFileName),
                    (giftRegistry.IsPrivate) ? 1 : 0, DB.SQuote(giftRegistry.GuestPassword), DB.SQuote(giftRegistry.CustomURLPostfix), DB.SQuote(giftRegistry.WebsiteCode), (giftRegistry.IsActive) ? 1 : 0), con);

                con.Close();
            }
        }

        public static string GetCustomURLByRegistryID(Guid registryId)
        {
            string retval = string.Empty;
            using (var con = new SqlConnection(DB.GetDBConn()))
            {
                con.Open();

                var reader = DB.GetRSFormat(con, "SELECT * FROM EcommerceGiftRegistry with (NOLOCK) WHERE RegistryID = {0}", DB.SQuote(registryId.ToString()));
                if (reader.Read())
                {
                    retval = DB.RSField(reader, "CustomURLPostfix");
                }

                reader.Close();
                con.Close();
            }

            return retval;
        }

        public static GiftRegistry GetGiftRegistryByRegistryID(Guid registryID, string webSiteCode)
        {
            GiftRegistry getRegistry = null;
            using (var con = new SqlConnection(DB.GetDBConn()))
            {
                con.Open();

                var reader = DB.GetRSFormat(con, "SELECT gr.*, (cc.ContactFirstName + ' ' + cc.ContactLastName) FullName FROM EcommerceGiftRegistry gr with (NOLOCK)" +                                                                                                       "INNER JOIN CRmContact cc with (NOLOCK) ON gr.ContactGuid = cc.ContactGuid WHERE gr.registryID = {0} AND gr.WebsiteCode={1}",
                                                 DB.SQuote(registryID.ToString()), DB.SQuote(webSiteCode));

                if (reader.Read()) { getRegistry = PopulateGiftRegistry(reader); }

                reader.Close();
                con.Close();
            }

            return getRegistry;
        }

        public static IEnumerable<GiftRegistryItem> GetGiftRegistryItemsByRegistryID(Guid registryID)
        {
            var lst = new List<GiftRegistryItem>();
            using (var con = new SqlConnection(DB.GetDBConn()))
            {
                con.Open();

                var reader = DB.GetRSFormat(con, "SELECT * FROM EcommerceGiftRegistryItem with (NOLOCK) WHERE registryID = {0}", DB.SQuote(registryID.ToString()));
                while (reader.Read())
                {
                    var getRegistryItem = new GiftRegistryItem()
                    {
                        Counter = DB.RSFieldInt(reader, "Counter"),
                        RegistryID = registryID,
                        RegistryItemCode = DB.RSFieldGUID(reader, "RegistryItemCode").TryParseGuid().Value,
                        ItemCode = DB.RSField(reader, "ItemCode"),
                        UnitMeasureCode = DB.RSField(reader, "UnitMeasureCode"),
                        Quantity = DB.RSFieldDecimal(reader, "Quantity"),
                        Ordered = DB.RSFieldDecimal(reader, "Ordered"),
                        Comment = DB.RSField(reader, "Comment").Trim(),
                        SortOrder = DB.RSFieldInt(reader, "SortOrder"),
                        GiftRegistryItemType = (GiftRegistryItemType)DB.RSFieldInt(reader, "RegistryItemType"),
                    };

                    lst.Add(getRegistryItem);
                }

                reader.Close();
                con.Close();
            }

            return lst.ToArray();

        }

        public static void UpdateGiftRegistry(GiftRegistry giftRegistry)
        {
            using (var con = new SqlConnection(DB.GetDBConn()))
            {
                con.Open();

                DB.ExecuteSQL(string.Format("UPDATE EcommerceGiftRegistry SET StartDate = {0}, EndDate = {1}, Title = {2}, Message = {3}, PictureFileName = {4}, IsPrivate = {5}, GuestPassword={6}, CustomURLPostfix = {7} WHERE RegistryID = {8}",
                    DB.SQuote(giftRegistry.StartDate.Value.ToString()),
                    DB.SQuote(giftRegistry.EndDate.Value.ToString()),
                    DB.SQuote(giftRegistry.Title),
                    DB.SQuote(giftRegistry.Message),
                    DB.SQuote(giftRegistry.PictureFileName),
                    (giftRegistry.IsPrivate) ? 1 : 0,
                    DB.SQuote(giftRegistry.GuestPassword),
                    DB.SQuote(giftRegistry.CustomURLPostfix),
                    DB.SQuote(giftRegistry.RegistryID.ToString())), con);

                con.Close();
            }
        }

        public static void DeductGiftRegistryItemQuantity(Guid giftRegistryID, Guid giftRegistryItemCode, decimal quantity, decimal ordered)
        {
            //decimal realQuantity = GiftRegistryDA.GetGiftRegistryItemQuantityByRegistryItemCode(giftRegistryItemCode);
            //if (realQuantity < quantity) { quantity = realQuantity; }
            using (var con = new SqlConnection(DB.GetDBConn()))
            {
                string query = string.Format("UPDATE EcommerceGiftRegistryItem SET Quantity = Quantity - {0}, Ordered = Ordered + {1} WHERE RegistryID = {2} AND RegistryItemCode= {3}",
                                                quantity,
                                                ordered,
                                                DB.SQuote(giftRegistryID.ToString()),
                                                DB.SQuote(giftRegistryItemCode.ToString()));
                con.Open();
                DB.ExecuteSQL(query, con);
                con.Close();
            }
        }

        public static void ActivateDeactivateRegistry(Guid registryID, bool active)
        {
            using (var con = new SqlConnection(DB.GetDBConn()))
            {
                con.Open();

                DB.ExecuteSQL(string.Format("UPDATE EcommerceGiftRegistry SET IsActive = {0} WHERE RegistryID = {1} ",
                    (active) ? 1 : 0, DB.SQuote(registryID.ToString())), con);
                con.Close();
            }
        }

        public static void AddGiftRegistryItems(GiftRegistryItem giftRegistryItem)
        {
            using (var con = new SqlConnection(DB.GetDBConn()))
            {
                con.Open();
                DB.ExecuteSQL(string.Format("INSERT INTO EcommerceGiftRegistryItem([RegistryItemCode], [RegistryID], [ItemCode], [UnitMeasureCode], [Quantity], [RegistryItemType], [Comment], [SortOrder]) values({0},{1},{2},{3},{4},{5},{6},{7})",
                    DB.SQuote(giftRegistryItem.RegistryItemCode.ToString()), DB.SQuote(giftRegistryItem.RegistryID.ToString()), DB.SQuote(giftRegistryItem.ItemCode),
                    DB.SQuote(giftRegistryItem.UnitMeasureCode), giftRegistryItem.Quantity, (int)giftRegistryItem.GiftRegistryItemType, DB.SQuote(giftRegistryItem.Comment),
                    giftRegistryItem.SortOrder, con));

                con.Close();
            }
        }

        public static void AddGiftRegistryItems(IEnumerable<GiftRegistryItem> giftRegistries)
        {
            using (var con = new SqlConnection(DB.GetDBConn()))
            {
                con.Open();

                foreach (var item in giftRegistries)
                {
                    DB.ExecuteSQL(string.Format("INSERT INTO EcommerceGiftRegistryItem([RegistryItemCode], [RegistryID], [ItemCode], [UnitMeasureCode], [Quantity], [RegistryItemType], [Comment], [SortOrder]) values({0},{1},{2},{3},{4},{5},{6},{7})",
                    DB.SQuote(item.RegistryItemCode.ToString()), DB.SQuote(item.RegistryID.ToString()), DB.SQuote(item.ItemCode),
                    DB.SQuote(item.UnitMeasureCode), item.Quantity, (int)item.GiftRegistryItemType, DB.SQuote(item.Comment),
                    item.SortOrder, con));
                }

                con.Close();
            }
        }

        public static void UpdateRegistryItem(GiftRegistryItem registryItem)
        {
            using (var con = new SqlConnection(DB.GetDBConn()))
            {
                con.Open();
                DB.ExecuteSQL(string.Format("UPDATE EcommerceGiftRegistryItem SET [UnitMeasureCode] = {0}, [Quantity] = {1}, [Comment] = {2}, [SortOrder] = {3} WHERE RegistryItemCode={4}",
                DB.SQuote(registryItem.UnitMeasureCode), registryItem.Quantity, DB.SQuote(registryItem.Comment), registryItem.SortOrder, DB.SQuote(registryItem.RegistryItemCode.ToString()), con));
                con.Close();
            }
        }

        public static void MoveRegistryItem(Guid targetRegistryID, Guid registryItemCode)
        {
            try
            {
                using (var con = new SqlConnection(DB.GetDBConn()))
                {
                    con.Open();

                    DB.ExecuteSQL(string.Format("UPDATE EcommerceGiftRegistryItem SET [RegistryID] = {0} WHERE RegistryItemCode = {1}",
                    DB.SQuote(targetRegistryID.ToString()), DB.SQuote(registryItemCode.ToString()), con));
                    con.Close();
                }
            }
            catch (Exception)
            {
                throw;
            }
        }

        public static GiftRegistryItem GetGiftRegistryItemByRegistryItemCode(Guid registryItemCode)
        {
            GiftRegistryItem registryItem = null;
            using (var con = new SqlConnection(DB.GetDBConn()))
            {
                con.Open();
                var reader = DB.GetRSFormat(con, "SELECT * FROM EcommerceGiftRegistryItem with (NOLOCK) WHERE RegistryItemCode = {0}", DB.SQuote(registryItemCode.ToString()));
                if (reader.Read())
                {
                    registryItem = new GiftRegistryItem()
                    {
                        Counter = DB.RSFieldInt(reader, "Counter"),
                        RegistryItemCode = registryItemCode,
                        ItemCode = DB.RSField(reader, "ItemCode"),
                        UnitMeasureCode = DB.RSField(reader, "UnitMeasureCode"),
                        Quantity = DB.RSFieldDecimal(reader, "Quantity"),
                        Ordered = DB.RSFieldDecimal(reader, "Ordered"),
                        Comment = DB.RSField(reader, "Comment").ToHtmlDecode(),
                        SortOrder = DB.RSFieldInt(reader, "SortOrder"),
                        GiftRegistryItemType = (GiftRegistryItemType)DB.RSFieldInt(reader, "RegistryItemType"),
                    };
                }

                reader.Close();
                con.Close();
            }

            return registryItem;
        }

        public static bool IsRegistryItemExists(Guid registryItemCode)
        {
            bool exist = false;
            using (var con = new SqlConnection(DB.GetDBConn()))
            {
                con.Open();
                var reader = DB.GetRSFormat(con, "SELECT Top 1 counter FROM EcommerceGiftRegistryItem with (NOLOCK) WHERE RegistryItemCode = {0}", DB.SQuote(registryItemCode.ToString()));
                exist = reader.Read();
                reader.Close();
                con.Close();
            }
            return exist;
        }

        public static decimal GetGiftRegistryItemQuantityByRegistryItemCode(Guid registryItemCode)
        {
            decimal quantity = 0;
            using (var con = new SqlConnection(DB.GetDBConn()))
            {
                con.Open();
                var reader = DB.GetRSFormat(con, "SELECT Quantity FROM EcommerceGiftRegistryItem with (NOLOCK) WHERE RegistryItemCode = {0}", DB.SQuote(registryItemCode.ToString()));
                if (reader.Read())
                {
                    quantity = DB.RSFieldDecimal(reader, "Quantity");
                }

                reader.Close();
                con.Close();
            }

            return quantity;
        }

        public static void DeleteRegistryItem(Guid registryItemCode)
        {
            using (var con = new SqlConnection(DB.GetDBConn()))
            {
                con.Open();
                DB.ExecuteSQL(string.Format("DELETE EcommerceGiftRegistryItem WHERE RegistryItemCode={0}", DB.SQuote(registryItemCode.ToString()), con));
                con.Close();
            }
        }

        public static void AddKitItem(Guid registryID, string customerCode, string itemKitCode, Guid registryItemCode, List<KitCartItem> kitCartItem)
        {
            AppLogic.AddKitItem(null, customerCode, itemKitCode, registryID, registryItemCode, kitCartItem);
        }

        public static IEnumerable<KitItemFromComposition> GetKitItemsFromComposition(Guid registryID, Guid registryItemCode)
        {
            var litKitItemFromComposition = new List<KitItemFromComposition>();
            using (var con = new SqlConnection(DB.GetDBConn()))
            {
                con.Open();

                var reader = DB.GetRSFormat(con, "SELECT * FROM EcommerceKitCart with (NOLOCK) WHERE giftregistryID = {0} AND RegistryItemCode = {1}",
                                                DB.SQuote(registryID.ToString()),
                                                DB.SQuote(registryItemCode.ToString()));

                while (reader.Read())
                {
                    litKitItemFromComposition.Add(new KitItemFromComposition()
                    {
                        GiftRegistryID = registryID,
                        GroupCode = DB.RSField(reader, "GroupCode"),
                        ItemCode = DB.RSField(reader, "ItemCode"),
                        ItemKitCode = DB.RSField(reader, "ItemKitCode"),
                        CustomerCode = DB.RSField(reader, "CustomerCode"),
                        CreatedOn = DB.RSFieldDateTime(reader, "CreatedOn"),
                    });
                }

                reader.Close();
                con.Close();
            }

            return litKitItemFromComposition;
        }

        public static void ClearKitItemsFromComposition(Guid registryID, Guid registryItemCode)
        {
            using (var con = new SqlConnection(DB.GetDBConn()))
            {
                con.Open();
                DB.ExecuteSQL(string.Format("DELETE EcommerceKitCart WHERE GiftRegistryID = {0} AND RegistryItemCode={1}",
                                DB.SQuote(registryID.ToString()),
                                DB.SQuote(registryItemCode.ToString()), con));
                con.Close();
            }
        }

        public static void MoveCompositionKitItems(Guid sourceGiftRegistryID, Guid targetGiftRegistryID, Guid registryItemCode)
        {
            using (var con = new SqlConnection(DB.GetDBConn()))
            {
                con.Open();
                DB.ExecuteSQL(string.Format("UPDATE EcommerceKitCart SET GiftRegistryID = {0} WHERE GiftRegistryID = {1} AND RegistryItemCode={2}",
                                DB.SQuote(targetGiftRegistryID.ToString()),
                                DB.SQuote(sourceGiftRegistryID.ToString()),
                                DB.SQuote(registryItemCode.ToString()), con));
                con.Close();
            }
        }

        public static bool IsRegistryPrefixExist(Guid? giftRegistryID, string prefix)
        {
            bool exist = false;
            using (var con = new SqlConnection(DB.GetDBConn()))
            {
                con.Open();

                string query = string.Format("SELECT top 1 * FROM ecommercegiftregistry with (NOLOCK) WHERE customUrlPostfix = {0}", DB.SQuote(prefix));
                if (giftRegistryID.HasValue)
                {
                    query = string.Format("SELECT top 1 * FROM ecommercegiftregistry with (NOLOCK) WHERE RegistryID NOT IN ({0}) AND customUrlPostfix = {1}",
                                            DB.SQuote(giftRegistryID.Value.ToString()),
                                            DB.SQuote(prefix));
                }

                var reader = DB.GetRSFormat(con, query);
                exist = reader.Read();
                reader.Close();
                con.Close();
            }

            return exist;
        }

        public static GiftRegistry GetRegistryByCustomUrl(string urlCustomUrlParam, string websiteCode)
        {
            GiftRegistry getRegistry = null;
            using (var con = new SqlConnection(DB.GetDBConn()))
            {
                con.Open();

                var reader = DB.GetRSFormat(con, "SELECT gr.*, (cc.ContactFirstName + ' ' + cc.ContactLastName) FullName FROM EcommerceGiftRegistry gr with (NOLOCK) " +
                                                 "INNER JOIN CRmContact cc with (NOLOCK) ON gr.ContactGuid = cc.ContactGuid " +
                                                 "WHERE gr.CustomURLPostfix = {0} AND gr.WebsiteCode = {1}", DB.SQuote(urlCustomUrlParam), DB.SQuote(websiteCode));
                if (reader.Read())
                {
                    getRegistry = PopulateGiftRegistry(reader);
                }

                reader.Close();
                con.Close();
            }

            return getRegistry;
        }

        public static GiftRegistryFindHeader FindRegistries(string firstName, string lastName, string eventTitle, int currentRowCount, string webSiteCode)
        {
            var header = new GiftRegistryFindHeader();
            var lst = new List<GiftRegistry>();
            using (var con = new SqlConnection(DB.GetDBConn()))
            {
                con.Open();

                string qry1 = string.Format("SELECT TOP {0} tblSearch.* FROM (", DomainConstants.DEFAULT_REGISTRY_PAGESIZE);
                string query2 = "SELECT {0} FROM EcommerceGiftRegistry gr with (NOLOCK) INNER JOIN " +
                                "CRmContact cc with (NOLOCK) ON gr.ContactGuid = cc.ContactGuid WHERE gr.IsPrivate = 0 AND gr.WebsiteCode = " + DB.SQuote(webSiteCode) +
                                " AND (";

                bool isNullFirstName = firstName.IsNullOrEmptyTrimmed();
                if (!isNullFirstName) { query2 += string.Format("cc.ContactFirstName like '%{0}%'", firstName); }

                bool isNullLastName = lastName.IsNullOrEmptyTrimmed();
                if (!isNullLastName)
                {
                    if (!isNullFirstName) { query2 += " OR "; }
                    query2 += string.Format("cc.ContactLastName like '%{0}%'", lastName);
                }

                if (!eventTitle.IsNullOrEmptyTrimmed())
                {
                    if (!isNullFirstName || !isNullLastName) { query2 += " OR "; }
                    query2 += string.Format("gr.title like '%{0}%'", eventTitle);
                }

                query2 += ") ";

                var countReader = DB.GetRSFormat(con, string.Format(query2, "COUNT(*) Total"));
                if (countReader.Read()) { header.TotalRecord = DB.RSFieldInt(countReader, "Total"); }
                countReader.Close();

                string query3 = ") AS tblSearch ORDER BY tblSearch.Title";
                string rowFilter = string.Empty;
                if (currentRowCount > 0)
                {
                    query3 = ") AS tblSearch {0} ORDER BY tblSearch.Title";
                    rowFilter = string.Format(query3, string.Format("WHERE tblSearch.[Row] > {0}", currentRowCount));
                }

                string finalQuery = string.Format(qry1 + query2 + ((currentRowCount > 0) ? rowFilter : query3), "ROW_NUMBER() OVER(ORDER BY gr.Title ASC) AS [Row], gr.*, (cc.ContactFirstName + ' ' + cc.ContactLastName) as FullName");
                var reader = DB.GetRSFormat(con, finalQuery);
                while (reader.Read())
                {
                    lst.Add(PopulateGiftRegistry(reader, true));
                }

                reader.Close();
                con.Close();
            }

            header.RawItems = lst;
            return header;
        }

        public static GiftRegistry PopulateGiftRegistry(IDataReader reader, bool isCustom = false)
        {
            var item = new GiftRegistry()
            {
                RegistryID = DB.RSFieldGUID(reader, "RegistryID").TryParseGuid().Value,
                WebsiteCode = DB.RSField(reader, "WebSiteCode"),
                Counter = DB.RSFieldInt(reader, "Counter"),
                Title = DB.RSField(reader, "Title"),
                StartDate = DB.RSFieldDateTime(reader, "StartDate"),
                EndDate = DB.RSFieldDateTime(reader, "EndDate"),
                Message = DB.RSField(reader, "Message").ToHtmlDecode(),
                IsPrivate = DB.RSFieldBool(reader, "IsPrivate"),
                GuestPassword = DB.RSField(reader, "GuestPassword"),
                PictureFileName = DB.RSField(reader, "PictureFileName"),
                CustomURLPostfix = DB.RSField(reader, "CustomURLPostfix"),
                ContactGUID = DB.RSFieldGUID(reader, "ContactGUID").TryParseGuid().Value,
                IsActive = DB.RSFieldBool(reader, "IsActive"),
                OwnersFullName = DB.RSField(reader, "FullName")
            };

            if (isCustom) { item.RowNumber = (int)DB.RSFieldLong(reader, "Row"); }

            return item;

        }

        public static void DeleteGiftRegistry(Guid registryId)
        {
            using (var con = new SqlConnection(DB.GetDBConn()))
            {
                con.Open();

                DB.ExecuteSQL(string.Format("DELETE EcommerceKitCart WHERE GiftRegistryID = {0} ",
                                DB.SQuote(registryId.ToString())), con);

                DB.ExecuteSQL(string.Format("DELETE EcommerceGiftRegistryItem WHERE RegistryID = {0} ",
                                DB.SQuote(registryId.ToString())), con);

                DB.ExecuteSQL(string.Format("DELETE ecommercegiftregistry WHERE RegistryID = {0} ",
                                DB.SQuote(registryId.ToString())), con);

                con.Close();
            }
        }

        public static void DeleteGiftRegistry(string commaDelimitedRegistryID)
        {
            using (var con = new SqlConnection(DB.GetDBConn()))
            {
                con.Open();

                DB.ExecuteSQL(string.Format("DELETE EcommerceKitCart WHERE GiftRegistryID IN ({0})", commaDelimitedRegistryID), con);

                DB.ExecuteSQL(string.Format("DELETE EcommerceGiftRegistryItem WHERE RegistryID IN ({0})", commaDelimitedRegistryID), con);

                DB.ExecuteSQL(string.Format("DELETE ecommercegiftregistry WHERE RegistryID IN ({0})", commaDelimitedRegistryID), con);

                con.Close();
            }
        }

        public static string GetPrimaryShippingAddressCodeOfOwnerByRegistryID(Guid registryId)
        {
            string shippingAddressCode = string.Empty;
            using (var con = new SqlConnection(DB.GetDBConn()))
            {
                con.Open();

                var reader = DB.GetRSFormat(con, "SELECT c.defaultshiptocode FROM EcommerceGiftRegistry egr with (NOLOCK) inner join CRMcontact cc with (NOLOCK) on egr.contactguid = cc.contactguid inner join customer c with (NOLOCK) on cc.entitycode = c.customercode where egr.registryid={0}", DB.SQuote(registryId.ToString()));
                if (reader.Read())
                {
                    shippingAddressCode = DB.RSField(reader, "defaultshiptocode");
                }

                reader.Close();
                con.Close();
            }

            return shippingAddressCode;
        }

        public static decimal? GetGiftRegistryItemQuantityByCartRecID(int recID)
        {
            decimal? quantity = null;
            using (var con = new SqlConnection(DB.GetDBConn()))
            {
                con.Open();

                string qry = string.Format("SELECT egr.Quantity FROM ecommerceShoppingCart esc with (NOLOCK) " +
                             "INNER JOIN ecommercegiftRegistryItem egr with (NOLOCK) on esc.RegistryItemCode = egr.RegistryItemCode " +
                             "WHERE esc.ShoppingCartRecID = {0}", recID);

                var reader = DB.GetRSFormat(con, qry);
                if (reader.Read())
                {
                    quantity = DB.RSFieldDecimal(reader, "Quantity");
                }

                reader.Close();
                con.Close();
            }
            return quantity;
        }

        public static bool IsRegistryExist(Guid registryId)
        {
            bool exist = false;
            using (var con = new SqlConnection(DB.GetDBConn()))
            {
                con.Open();
                var reader = DB.GetRSFormat(con, string.Format("SELECT top 1 Counter FROM ecommercegiftregistry with (NOLOCK) WHERE RegistryID = {0}", DB.SQuote(registryId.ToString())));
                exist = reader.Read();
                reader.Close();
                con.Close();
            }
            return exist;
        }

        /// <summary>
        /// Delete all the Registry items from cart.
        /// </summary>
        /// <param name="commaDelimitedCodes">Comma Delimited Gift Registry Item Codes</param>
        public static void RemoveRegistryItems(string commaDelimitedCodes)
        {
            using (var con = new SqlConnection(DB.GetDBConn()))
            {
                con.Open();
                DB.ExecuteSQL(string.Format("DELETE EcommerceShoppingCart WHERE RegistryItemCode IN ({0})", commaDelimitedCodes), con);
                con.Close();
            }
        }

        public static void RemoveDeletedRegistryItemsFromCart(string commaDelimitedCodes)
        {
            RemoveRegistryItems(commaDelimitedCodes);
        }

        public static IEnumerable<string> DeleteExpiredRegistryItemsReturnsPictureFileName(int extraDays, string websiteCode)
        {
            var lstFileNames = new List<string>();
            var lstRegIds = new List<string>();

            using (var con = new SqlConnection(DB.GetDBConn()))
            {
                con.Open();
                string query = string.Format("SELECT PictureFileName, RegistryID FROM EcommerceGiftRegistry with (NOLOCK) WHERE getdate() > DATEADD(day,{0}, enddate) AND WebsiteCode = {1}", extraDays, DB.SQuote(websiteCode));
                var reader = DB.GetRS(query,con);
                while (reader.Read())
                {
                    string fileName = DB.RSField(reader, "PictureFileName").Trim();

                    //do not add if registry has no picture
                    if (fileName != DomainConstants.DEFAULT_NO_PIC_FILENAME) { lstFileNames.Add(fileName); }

                    lstRegIds.Add(DB.SQuote(DB.RSFieldGUID(reader, "RegistryID").TryParseGuid().Value.ToString()));
                }
                reader.Close();

                if(lstRegIds.Count() > 0)
                {
                    DeleteGiftRegistry(string.Join(",", lstRegIds.Where(id => !id.IsNullOrEmptyTrimmed()).AsParallel()));
                }

                con.Close();
            }

            return lstFileNames;
        }

    }
}